﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI.WebControls;
using System.IO;
using System.Xml;


/// <summary>
///GridViewToXML 的摘要说明
/// </summary>
public class GridViewToXML
{
    public GridViewToXML()
    {
        //
        //TODO: 在此处添加构造函数逻辑
        //
    }

    #region 将GridView中的数据导出为Xml文件

    /// <summary>
    /// 将GridView中的数据导出为Xml文件
    /// </summary>
    /// <param name="gv">需要处理的GridView</param>
    /// <param name="filePath">xml文件存放路径</param>
    public static void ToXml(GridView gv, string filePath)
    {

        FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.ReadWrite);
        StreamWriter sw = new StreamWriter(fs);

        #region 添加xml头 并指定excel打开

        string xmlStr = "<?xml version='1.0'?>" +//xml头文件
"<?mso-application progid='Excel.Sheet'?>" +//默认以Excel打开
"<Workbook xmlns='urn:schemas-microsoft-com:office:spreadsheet' " +
 "xmlns:o='urn:schemas-microsoft-com:office:office' " +
 "xmlns:x='urn:schemas-microsoft-com:office:excel' " +
 "xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet' " +
 "xmlns:html='http://www.w3.org/TR/REC-html40'>";

        xmlStr += "<DocumentProperties xmlns='urn:schemas-microsoft-com:office:office'></DocumentProperties>" +
 "<OfficeDocumentSettings xmlns='urn:schemas-microsoft-com:office:office'></OfficeDocumentSettings>" +
 "<ExcelWorkbook xmlns='urn:schemas-microsoft-com:office:excel'></ExcelWorkbook>" +
 "<Styles> " +
 "<Style ss:ID='Default' ss:Name='Normal'>" +
     "<Alignment ss:Vertical='Center' ss:Horizontal='Center'/>" +
        "<Borders/>" +
        "<Font ss:FontName='宋体' x:CharSet='134' ss:Size='11' ss:Color='#000000'/><Interior/>" +
   "<NumberFormat/>" +
   "<Protection/>" +
  "</Style>" +
      "</Styles>";


        sw.Write(xmlStr);
        xmlStr = string.Empty;

        #endregion

        //新建工作表
        xmlStr += "<Worksheet ss:Name='Sheet1'>";
        //新建表格
        xmlStr += "<Table x:FullColumns='1' x:FullRows='1' ss:DefaultColumnWidth='54' ss:DefaultRowHeight='13.5'>";

        sw.WriteLine(xmlStr);
        xmlStr = string.Empty;

        /***************************************/
        /***************添加表头****************/
        /***************************************/
        string a1 = "";
        xmlStr += "<Row>";
        for (int i = 0; i < gv.HeaderRow.Cells.Count; i++)
        {
            a1 += "<tr><th>" + gv.HeaderRow.Cells[i].Text;
            xmlStr += "<Cell ";
            TableCell cell = gv.HeaderRow.Cells[i];

            //跨列属性可能为添加了html属性colspan，也可能是由cell的ColumnSpan属性指定
            int colSpan = -1;
            if (cell.Attributes["colspan"] != null && cell.Attributes["colspan"] != string.Empty)
            {
                colSpan = int.Parse(cell.Attributes["colspan"].ToString());
            }
            if (cell.ColumnSpan > 1)
            {
                colSpan = cell.ColumnSpan;
            }
            if (colSpan > 0)
            {
                colSpan--;
                xmlStr += "ss:MergeAcross='" + colSpan.ToString() + "'";
            }
            xmlStr += " ><Data ss:Type='String'>";
            string strHeader = gv.HeaderRow.Cells[i].Text;

            if (strHeader.Contains("</th></tr><tr>"))//换行标记
            {
                xmlStr += strHeader.Substring(0, strHeader.Length - 14) + "</Data></Cell></Row><Row>";
            }
            else
            {
                xmlStr += strHeader + "</Data></Cell>";
            }
        }
        xmlStr += "</Row>";


        sw.WriteLine(xmlStr);
        xmlStr = string.Empty;

        /***************************************/
        /***************添加数据****************/
        /***************************************/

        int rowCount = gv.Rows.Count;

        //gv.HeaderRow.Cells.Count属性始终为0，可能为控件bug。循环控件表头遍历出列数
        int colCOunt = 0;
        foreach (TableCell cell in gv.HeaderRow.Cells)
        {
            if (cell.Text.Contains("</th></tr><tr>"))
            {
                colCOunt = 0;
                continue;
            }
            else
            {
                colCOunt++;
            }
        }

        for (int i = 0; i < rowCount; i++)
        {
            xmlStr += "<Row>";

            bool flagPreviousVisible = true;
            for (int j = 0; j < colCOunt; j++)
            {
                if (gv.Rows[i].Cells[j].Visible == false)
                {
                    flagPreviousVisible = false;
                    continue;
                }

                #region 判断是否为矩形单元格中的单元格

                if (i > 0 && j > 0)
                {
                    bool flagSkip = false;
                    int tempx = -1;//记录矩形单元格的左下角位置
                    for (int l = j - 1; l >= 0; l--)
                    {
                        if (gv.Rows[i].Cells[l].Visible == false)
                        {
                            tempx = l;
                            break;
                        }
                        else
                        {
                            continue;
                        }
                    }
                    if (tempx > -1)//存在可能的矩形单元格
                    {
                        for (int m = i - 1; m >= 0; m--)
                        {
                            int colSpan = j - tempx + 1;
                            int rowSpan = i - m + 1;
                            if (gv.Rows[m].Cells[tempx].ColumnSpan >= colSpan && gv.Rows[m].Cells[tempx].RowSpan >= rowSpan)//存在矩形单元格，即当前单元格属于矩形单元格中的一部分
                            {
                                flagPreviousVisible = false;//模拟前一单元格不可见（为添加Index属性）
                                flagSkip = true;
                                break;//满足条件，跳出循环
                            }
                            else
                            {
                                continue;
                            }
                        }
                    }
                    if (flagSkip)//存在矩形单元格，且该单元格存在于该矩形单元格，跳过此单元格拼接字符串
                    {
                        continue;
                    }
                }

                #endregion

                int MergeAcross = gv.Rows[i].Cells[j].ColumnSpan;//跨列，即合并的列数
                int MergeDown = gv.Rows[i].Cells[j].RowSpan;//跨行，即合并的行数

                xmlStr += "<Cell ";

                if (MergeAcross > 0)//存在要合并的行
                {
                    xmlStr += "ss:MergeAcross='" + --MergeAcross + "' ";//ss:MergeAcross 合并列 
                }
                if (MergeDown > 0)//存在要合并的列
                {
                    xmlStr += "ss:MergeDown='" + --MergeDown + "' ";//ss:MergeDown 合并行
                }


                if (flagPreviousVisible == false)
                {
                    int a = j + 1;
                    xmlStr += "ss:Index='" + a + "' ";
                    flagPreviousVisible = true;
                }
                xmlStr += " >";
                xmlStr += "<Data ss:Type='String'>" + gv.Rows[i].Cells[j].Text + "</Data>";//
                xmlStr += "</Cell>";
            }
            xmlStr += "</Row>";

            sw.WriteLine(xmlStr);
            xmlStr = string.Empty;
        }

        xmlStr += "</Table>";

        sw.WriteLine(xmlStr);
        xmlStr = string.Empty;

        #region 添加xml打印格式等属性，完成xml字符串拼接

        //WorksheetOptions主要定义 打印方式  边距 窗口冻结 等等 ,在设计 Excel文件时都设置好了  不需要修改
        xmlStr += "<WorksheetOptions xmlns='urn:schemas-microsoft-com:office:excel'>" +
   "<PageSetup>" +
    "<Header x:Margin='0.3'/>" +
    "<Footer x:Margin='0.3'/>" +
    "<PageMargins x:Bottom='0.75' x:Left='0.7' x:Right='0.7' x:Top='0.75'/>" +
   "</PageSetup>" +
   "<Print>" +
    "<ValidPrinterInfo/>" +
    "<PaperSizeIndex>9</PaperSizeIndex>" +
    "<HorizontalResolution>200</HorizontalResolution>" +
    "<VerticalResolution>200</VerticalResolution>" +
   "</Print>" +
   "<Selected/>" +
            /*
             * 
             *这段代码加载<Selected>标签之后，用于冻结窗口
             * 
             * <FreezePanes/>
             * <FrozenNoSplit/>
             * <SplitHorizontal>3</SplitHorizontal>//冻结的窗口行数             
             * <ActivePane>2</ActivePane>
             *                                        
             */

   "<Panes>" +
    "<Pane>" +
     "<Number>3</Number>" +
     "<ActiveRow>2</ActiveRow>" +
     "<RangeSelection>R3C1:R4C1</RangeSelection>" +
    "</Pane>" +
   "</Panes>" +
   "<ProtectObjects>False</ProtectObjects>" +
   "<ProtectScenarios>False</ProtectScenarios>" +
  "</WorksheetOptions>";
        xmlStr += "</Worksheet>";
        xmlStr += "</Workbook>";

        #endregion

        sw.Write(xmlStr);
        xmlStr = string.Empty;

        sw.Close();
        fs.Close();

        //XmlDocument xmldoc = new XmlDocument(); //创建空的XML文档
        //xmldoc.LoadXml(xmlStr);
        //xmldoc.Save(Server.MapPath("temp.xml")); //保存 
    }


    #endregion

    #region 该方法未导出真正的Excel

    /// <summary>
    /// XmlDocument转换xml文件
    /// </summary>
    /// <param name="gv"></param>
    /// <param name="filePath">Server.MapPath("bookstore.xml")</param>
    public void ToXMLMethod1(GridView gv, string filePath)
    {
        XmlText xmlText;
        XmlDocument xmlDoc = new XmlDocument();

        //加入XML的声明段落
        XmlNode xmlNode = xmlDoc.CreateXmlDeclaration("1.0", "UTF-8", null);
        xmlDoc.AppendChild(xmlNode);

        //添加一个根元素
        XmlElement xmlTable = xmlDoc.CreateElement("", "table", string.Empty);
        xmlText = xmlDoc.CreateTextNode("");
        xmlTable.AppendChild(xmlText);
        xmlDoc.AppendChild(xmlTable);


        //添加了自定义表头
        if (gv.HeaderRow.Cells.Count > 0)
        {
            TableCellCollection cellCollection = gv.HeaderRow.Cells;


            XmlElement xmlHeader = xmlDoc.CreateElement("", "tr", string.Empty);
            xmlText = xmlDoc.CreateTextNode("");
            xmlHeader.AppendChild(xmlText);
            xmlDoc.ChildNodes.Item(1).AppendChild(xmlHeader);
            for (int i = 0; i < gv.HeaderRow.Cells.Count; i++)
            {
                XmlElement xmlTd = xmlDoc.CreateElement("", "td", string.Empty);
                xmlText = xmlDoc.CreateTextNode(cellCollection[i].Text);
                xmlTd.AppendChild(xmlText);
                xmlTd.SetAttribute("RowSpan", gv.HeaderRow.Cells[i].RowSpan.ToString());
                xmlTd.SetAttribute("ColumnSpan", gv.HeaderRow.Cells[i].ColumnSpan.ToString());
                xmlDoc.ChildNodes.Item(1).AppendChild(xmlHeader).AppendChild(xmlTd);
            }
        }



        for (int i = 0; i < gv.Rows.Count; i++)
        {
            //加入子元素tr
            XmlElement xmlTr = xmlDoc.CreateElement("", "tr", string.Empty);
            xmlText = xmlDoc.CreateTextNode("");
            xmlTr.AppendChild(xmlText);
            xmlDoc.ChildNodes.Item(1).AppendChild(xmlTr);

            for (int j = 0; j < gv.Rows[i].Cells.Count; j++)
            {
                TableCell cell = gv.Rows[i].Cells[j];

                XmlElement xmlTd = xmlDoc.CreateElement("", "td", string.Empty);
                xmlText = xmlDoc.CreateTextNode(cell.Text);
                xmlTd.AppendChild(xmlText);
                xmlTd.SetAttribute("RowSpan", cell.RowSpan.ToString());
                xmlTd.SetAttribute("ColumnSpan", cell.ColumnSpan.ToString());
                xmlDoc.ChildNodes.Item(1).AppendChild(xmlTr).AppendChild(xmlTd);
            }
        }

        xmlDoc.Save(filePath); //保存 
    }
    #endregion

    #region GridView合并单元格操作


    #region 合并成为矩形单元格
    /// <summary>
    /// 合并成为矩形单元格
    /// </summary>
    /// <param name="GridView1"></param>
    /// <param name="sRow">开始行索引</param>
    /// <param name="sCol">开始列索引</param>
    /// <param name="eRow">结束行索引</param>
    /// <param name="eCol">结束列索引</param>
    public static void GroupRectangle(GridView GridView1, int sRow, int sCol, int eRow, int eCol)
    {
        if (sRow < 0 || sRow > eRow || eRow > GridView1.Rows.Count - 1)
        {
            return;
        }
        if (sCol < 0 || sCol > eCol || eCol > GridView1.Columns.Count - 1)
        {
            return;
        }
        TableCell oldTc = GridView1.Rows[sRow].Cells[sCol];
        oldTc.ColumnSpan = eCol - sCol + 1;
        oldTc.RowSpan = eRow - sRow + 1;
        //oldTc.ColumnSpan = eCol - sCol > 0 ? eCol - sCol : 1;
        //oldTc.RowSpan = eRow - sRow > 0 ? eRow - sRow : 1;
        for (int i = sRow; i <= eRow; i++)
        {
            for (int j = sCol; j <= eCol; j++)
            {
                if (i == sRow && j == sCol)//起始单元格
                {
                    //oldTc.ColumnSpan = 1;
                    //oldTc.RowSpan = 1;
                    continue;
                }

                TableCell tc = GridView1.Rows[i].Cells[j];
                tc.Visible = false;
            }
        }
        oldTc.Visible = true;
        oldTc.VerticalAlign = VerticalAlign.Middle;
        oldTc.HorizontalAlign = HorizontalAlign.Center;
    }
    #endregion

    #region 合并单元格 合并某一行的所有列
    ///　 <summary>　
    ///　 合并GridView中某行相同信息的行（单元格）
    ///　 </summary>　
    ///　 <param　 name="GridView1">GridView对象</param>　
    ///　 <param　 name="cellNum">需要合并的行</param>
    public static void GroupRow(GridView GridView1, int rows)
    {
        TableCell oldTc = GridView1.Rows[rows].Cells[0];
        for (int i = 1; i < GridView1.Rows[rows].Cells.Count; i++)
        {
            TableCell tc = GridView1.Rows[rows].Cells[i];　 //Cells[0]就是你要合并的列
            if (oldTc.Text == tc.Text)
            {
                tc.Visible = false;
                if (oldTc.ColumnSpan == 0)
                {
                    oldTc.ColumnSpan = 1;
                }
                oldTc.ColumnSpan++;
                oldTc.VerticalAlign = VerticalAlign.Middle;
            }
            else
            {
                oldTc = tc;
            }
        }
    }
    #endregion
    #region 合并单元格 合并一行中的几列
    /// <summary>
    /// 合并单元格 合并一行中的几列
    /// </summary>
    /// <param name="GridView1">GridView ID</param>
    /// <param name="rows">行</param>
    /// <param name="sCol">开始列</param>
    /// <param name="eCol">结束列</param>
    public static void GroupRow(GridView GridView1, int rows, int sCol, int eCol)
    {
        TableCell oldTc = GridView1.Rows[rows].Cells[sCol];
        for (int i = 1; i < eCol - sCol; i++)
        {
            TableCell tc = GridView1.Rows[rows].Cells[i + sCol];　 //Cells[0]就是你要合并的列
            tc.Visible = false;
            if (oldTc.ColumnSpan == 0)
            {
                oldTc.ColumnSpan = 1;
            }
            oldTc.ColumnSpan++;
            oldTc.VerticalAlign = VerticalAlign.Middle;
        }
    }
    #endregion
    #region 合并单元格 合并某一列所有行
    /// <summary>
    /// 合并GridView中某列相同信息的行（单元格）
    /// </summary>
    /// <param name="GridView1"></param>
    /// <param name="cellNum"></param>
    public static void GroupCol(GridView GridView1, int cols)
    {
        if (GridView1.Rows.Count < 1 || cols > GridView1.Rows[0].Cells.Count - 1)
        {
            return;
        }
        TableCell oldTc = GridView1.Rows[0].Cells[cols];
        for (int i = 1; i < GridView1.Rows.Count; i++)
        {
            TableCell tc = GridView1.Rows[i].Cells[cols];
            if (oldTc.Text == tc.Text)
            {
                tc.Visible = false;
                if (oldTc.RowSpan == 0)
                {
                    oldTc.RowSpan = 1;
                }
                oldTc.RowSpan++;
                oldTc.VerticalAlign = VerticalAlign.Middle;
            }
            else
            {
                oldTc = tc;
            }
        }
    }
    #endregion
    #region 合并单元格 合并某一列中的某些行
    /// <summary>
    /// 合并单元格 合并某一列中的某些行
    /// </summary>
    /// <param name="GridView1">GridView ID</param>
    /// <param name="cellNum">列</param>
    /// <param name="sRow">开始行</param>
    /// <param name="eRow">结束列</param>
    public static void GroupCol(GridView GridView1, int cols, int sRow, int eRow)
    {
        if (GridView1.Rows.Count < 1 || cols > GridView1.Columns.Count - 1)
        {
            return;
        }
        TableCell oldTc = GridView1.Rows[sRow].Cells[cols];
        for (int i = 1; i < eRow - sRow; i++)
        {
            TableCell tc = GridView1.Rows[sRow + i].Cells[cols];
            tc.Visible = false;
            if (oldTc.RowSpan == 0)
            {
                oldTc.RowSpan = 1;
            }
            oldTc.RowSpan++;
            oldTc.VerticalAlign = VerticalAlign.Middle;
        }
    }
    #endregion

    #endregion
}
